package com.yjd.bi.demo;

import java.util.List;
import java.util.Map;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.yjd.bi.report.model.BiDatabaseInfoModel;
import com.yjd.bi.report.model.BiTableInfoModel;
import com.yjd.bi.report.util.BiUtil;
import com.yjd.comm.util.FrameUtil;

public class Demo3 {
	@SuppressWarnings("unchecked")
	static Map<String, JdbcTemplate> jdbcTemplateMap = FrameUtil.newHashMap();
	static JdbcTemplate jdbcTemplate = null;
	static {
		jdbcTemplate = getJdbcTemplate(
				"jdbc:mysql://192.168.11.13:3306/www_yijiedai_com?characterEncoding=UTF-8",
				"www_yijiedai_com", "yijiedai123");
	}
	
	public static JdbcTemplate getJdbcTemplate(String url,String username,String password){
		String key = String.format("url:%s;username:%s;password:%s", url,username,password);
		JdbcTemplate jt = jdbcTemplateMap.get(key);
		if(jt==null){
			DataSource dataSource = new DataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource
					.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);
			dataSource.setInitialSize(5);
			dataSource.setMaxActive(300);
			dataSource.setMinIdle(5);
			dataSource.setMaxIdle(20);
			dataSource.setTestOnBorrow(true);
			dataSource.setTestOnReturn(false);
			dataSource.setTestWhileIdle(true);
			dataSource.setValidationQuery("select 1");
			dataSource.setMaxWait(5000);
			dataSource.setTimeBetweenEvictionRunsMillis(600000);
			dataSource.setMinEvictableIdleTimeMillis(1800000);
			jt = new JdbcTemplate(dataSource);
			jdbcTemplateMap.put(key, jt);
		}
		return jt;
	}
	
	public static BiDatabaseInfoModel getDbByCode(String code){
		return getJdbcTemplate(
				"jdbc:mysql://192.168.11.13:3306/bi_yijiedai_com?characterEncoding=UTF-8",
				"www_yijiedai_com", "yijiedai123")
				.queryForObject("select * from yjd_bi_database_info where code = ?",new Object[]{code},
						new BeanPropertyRowMapper<BiDatabaseInfoModel>(BiDatabaseInfoModel.class));
	}

	public static class EtlModel {
		private Long id;
		private String fromDb;
		private String fromTableName;
		private String logDbCode;
		private String pkField;
		private String toDb;
		private String toTableName;

		public EtlModel() {
		}

		public EtlModel(Long id, String fromDb, String fromTableName,String logDbCode,
				String pkField, String toDb, String toTableName) {
			super();
			this.id = id;
			this.fromDb = fromDb;
			this.fromTableName = fromTableName;
			this.logDbCode = logDbCode;
			this.pkField = pkField;
			this.toDb = toDb;
			this.toTableName = toTableName;
		}

		public Long getId() {
			return id;
		}

		public void setId(Long id) {
			this.id = id;
		}

		public String getFromDb() {
			return fromDb;
		}

		public void setFromDb(String fromDb) {
			this.fromDb = fromDb;
		}

		public String getFromTableName() {
			return fromTableName;
		}

		public void setFromTableName(String fromTableName) {
			this.fromTableName = fromTableName;
		}
		
		public String getLogDbCode() {
			return logDbCode;
		}

		public void setLogDbCode(String logDbCode) {
			this.logDbCode = logDbCode;
		}

		public String getPkField() {
			return pkField;
		}

		public void setPkField(String pkField) {
			this.pkField = pkField;
		}

		public String getToDb() {
			return toDb;
		}

		public void setToDb(String toDb) {
			this.toDb = toDb;
		}

		public String getToTableName() {
			return toTableName;
		}

		public void setToTableName(String toTableName) {
			this.toTableName = toTableName;
		}
	}

	public static List<EtlModel> getTableList() {
		return jdbcTemplate
				.query("select a.id,d.code fromDb,b.table_name fromTableName,f.code logDbCode,b.pk_field_name pkField,e.code toDb,c.table_name toTableName from bi_yijiedai_com.yjd_bi_etl_fromtable_totable a,bi_yijiedai_com.yjd_bi_table_info b,bi_yijiedai_com.yjd_bi_table_info c,bi_yijiedai_com.yjd_bi_database_info d,bi_yijiedai_com.yjd_bi_database_info e,bi_yijiedai_com.yjd_bi_database_info f where a.from_table_id = b.id and a.to_table_id = c.id and b.database_info_id = d.id and c.database_info_id = e.id and d.data_change_log_database_info_id = f.id",
						new BeanPropertyRowMapper(EtlModel.class));
	}

	public static void main(String[] args) {
		List<EtlModel> etls = getTableList();
		String s = "create trigger 表名_trigger_insert\n"
				+ "after insert on 表名\n"
				+ "FOR EACH ROW\n"
				+ "begin\n"
				+ "INSERT INTO 数据变更日志库.yjd_data_change_record (database_code, table_name, ref_id, type, create_time, status, actual_dispose_time, version) VALUES ('所在业务库编码', '表名', new.主键名称, 1, unix_timestamp(now()), 0, 0, 1);\n"
				+ "end;\n"
				+ "\n"
				+ "create trigger 表名_trigger_delete\n"
				+ "after delete on 表名\n"
				+ "FOR EACH ROW\n"
				+ "begin\n"
				+ "INSERT INTO 数据变更日志库.yjd_data_change_record (database_code, table_name, ref_id, type, create_time, status, actual_dispose_time, version) VALUES ('所在业务库编码', '表名', old.主键名称, 2, unix_timestamp(now()), 0, 0, 1);\n"
				+ "end;\n"
				+ "\n"
				+ "create trigger 表名_trigger_update\n"
				+ "after update on 表名\n"
				+ "FOR EACH ROW\n"
				+ "begin\n"
				+ "INSERT INTO 数据变更日志库.yjd_data_change_record (database_code, table_name, ref_id, type, create_time, status, actual_dispose_time, version) VALUES ('所在业务库编码', '表名', old.主键名称, 3, unix_timestamp(now()), 0, 0, 1);\n"
				+ "end;";

		String dropsql = "drop trigger if exists 表名_trigger_insert;\n"
				+ "drop trigger if exists 表名_trigger_delete;\n"
				+ "drop trigger if exists 表名_trigger_update;";
		boolean flag = true;
		for (EtlModel etl : etls) {
			System.out.println("/*在" + etl.getFromDb() + "库中运行*/");
			System.out.println("/*表" + etl.getFromDb() + "."
					+ etl.getFromTableName() + "删除触发器 start*/");
			System.out.println(dropsql.replaceAll("表名", etl.getFromDb() + "."
					+ etl.getFromTableName()));
			System.out.println("/*表" + etl.getFromDb() + "."
					+ etl.getFromTableName() + "删除触发器 end*/");
			System.out.println();
			if (flag) {
				continue;
			}
			// 数据转移
			System.out.println("/*在bi_yijiedai_com库中运行*/");
			System.out.println("truncate table " + etl.getToDb() + "."
					+ etl.getToTableName() + ";");
			List<String> columnNames = BiUtil.getColumnNames(
					etl.getFromTableName(),
					BiUtil.getJdbcTemplate(getDbByCode(etl.getFromDb())));
			StringBuilder sql = new StringBuilder("insert into "
					+ etl.getToDb() + "." + etl.getToTableName() + " (");
			StringBuilder colSql = new StringBuilder();
			int colCount = columnNames.size();
			for (int i = 0; i < colCount; i++) {
				colSql.append("`").append(columnNames.get(i)).append("`");
				if (i < colCount - 1) {
					colSql.append(",");
				}
			}
			sql.append(colSql.toString()).append(")");
			sql.append(" select ").append(colSql).append(" from ")
					.append(etl.getFromDb() + "." + etl.getFromTableName())
					.append(";");
			System.out.println(sql);
			System.out.println("/*在bi_yijiedai_com库中执行，更新当前抽取数据的最大主键id*/");
			System.out
					.println(String
							.format("update bi_yijiedai_com.yjd_bi_etl_fromtable_totable set etl_all_max_uq_value = (select max(%s) from %s) where id = %s;",
									etl.getPkField(), etl.getToTableName(),
									etl.getId()));
		}
		for (EtlModel etl : etls) {
			System.out.println("/*在" + etl.getFromDb() + "库中运行*/");
			System.out.println("/*表" + etl.getFromDb() + "."
					+ etl.getFromTableName() + "创建触发器 start*/");
			System.out.println(s.replaceAll("表名", etl.getFromTableName())
					.replaceAll("数据变更日志库", etl.getLogDbCode())
					.replaceAll("主键名称", etl.getPkField())
					.replaceAll("所在业务库编码", etl.getFromDb()));
			System.out.println("/*表" + etl.getFromDb() + "."
					+ etl.getFromTableName() + "创建触发器 end*/");
			System.out.println();
		}
		/*
		 * for (Map.Entry<String, String> entry : map.entrySet()) {
		 * System.out.println(String.format(
		 * "select * from %s a order by a.%s desc limit 1;", etl.getTableName(),
		 * etl.getPkField())); }
		 * 
		 * for (Map.Entry<String, String> entry : map.entrySet()) {
		 * System.out.println
		 * (String.format("select count(*) from %s a union all",
		 * etl.getTableName())); }
		 */
		int i = 1;
		for (EtlModel etl : etls) {
			System.out
					.println(String.format(
							"将%s库中的%s表数据复制到%s库中的%s表中，复制完毕之后在%s库中执行脚本：%s",
							etl.getFromDb(),
							etl.getFromTableName(),
							etl.getToDb(),
							etl.getToTableName(),
							etl.getToDb(),
							String.format(
									"update bi_yijiedai_com.yjd_bi_etl_fromtable_totable set etl_all_max_uq_value = (select max(%s) from %s), is_activie = 1 where id = %s;",
									etl.getPkField(), etl.getToTableName(),
									etl.getId())));
		}

		for (EtlModel etl : etls) {
			// 数据转移
			System.out.println("/*在bi_yijiedai_com库中运行*/");
			System.out.println("truncate table " + etl.getToDb() + "."
					+ etl.getToTableName() + ";");
			List<String> columnNames = BiUtil.getColumnNames(
					etl.getFromTableName(),
					BiUtil.getJdbcTemplate(getDbByCode(etl.getFromDb())));
			StringBuilder sql = new StringBuilder("insert into "
					+ etl.getToDb() + "." + etl.getToTableName() + " (");
			StringBuilder colSql = new StringBuilder();
			int colCount = columnNames.size();
			for (i = 0; i < colCount; i++) {
				colSql.append("`").append(columnNames.get(i)).append("`");
				if (i < colCount - 1) {
					colSql.append(",");
				}
			}
			sql.append(colSql.toString()).append(")");
			sql.append(" select ").append(colSql).append(" from ")
					.append(etl.getFromDb() + "." + etl.getFromTableName())
					.append(";");
			System.out.println(sql);
			System.out.println("/*在bi_yijiedai_com库中执行，更新当前抽取数据的最大主键id*/");
			System.out
					.println(String
							.format("update bi_yijiedai_com.yjd_bi_etl_fromtable_totable set etl_all_max_uq_value = (select max(%s) from %s) where id = %s;",
									etl.getPkField(), etl.getToTableName(),
									etl.getId()));
		}
	}
}
